import sqlexecx as db
from config import MYSQL

if __name__ == '__main__':
    select_key = "SELECT last_insert_rowid()"
    # db.init(**MYSQL)
    db.init('test.db', driver='sqlite3', show_sql=True, debug=True)

    table = db.table('person')

    table.save(name='张三', age=3)
    table.save_select_key(select_key, name='张三', age=4)
    # print(db.table('person').get('count(1)'))
    # print(db.table('person').count())
    # for r in db.table('person').select():
    #     print(r)
    # print(db.table('person').select_one('name', 'age'))
    # for r in db.table('person').query():
    #     print(r)
    # print(db.table('person').query_one('name', 'age'))

    # print(db.table('person').where(name='张三').get('count(1)'))
    # print(db.table('person').where(name='张三').count())
    for r in db.table('person').where(name='张三', limit=3).select():
        print(r)
    # db.table('person').where(name='李四').delete()
    table.where(name='张三', id=3).update(name='李四', age=45)
    # print(db.table('person').where(name='李四').select_one('name', 'age'))
    for r in db.table('person').columns('id, name, age').where(name='李四').query():
        print(r)
    # print(db.table('person').query_one('name', 'age'))
    for r in db.table('person').where(name='李四').columns('id, name, age').select():
        print(r)

    print(db.table('person').page(2, 2).select('id, name, age'))
    print(db.table('person').page(2, 2).query('id, name, age'))

    print(db.table('person').columns('id, name, age').page(2, 2).select())
    print(db.table('person').columns('id, name, age').page(2, 2).query())

    print(db.table('person').where(name='张三').page(2, 2).select('id, name, age'))
    print(db.table('person').where(name='张三').page(2, 2, True).query('id, name, age'))

    print(db.table('person').columns('id, name, age').where(name='李四').page(2, 2).select())
    print(db.table('person').where(name='李四').page(2, 2).query())

    print(db.table('person').where(name='李四').exists())
    db.table('person').where(name='李四').update(name='lissssss')
    db.table('person').where(name='lissssss').delete()

    args = [
        ('addd', 55, '1968-10-08', 0, 1.0, 20.5, 854.56),
        ('bbb', 55, '1968-10-08', 0, 1.0, 20.5, 854.56)
    ]
    db.table('person').columns('name', 'age', 'birth_date', 'sex', 'grade', 'point', 'money').insert(*args[0])
    print(db.table('person').columns('name', 'age', 'birth_date', 'sex', 'grade', 'point', 'money').batch_insert(*args))
    print(db.table('person').columns('id, name, age').order_by('id DESC, name ASC').limit(3).select())
    print(db.table('person').where(name='张三').order_by('id DESC, name ASC').limit(3).query())
    print(db.table('person').columns('id, name, age').where(name='张三').limit(3).query())
    print(db.table('person').columns('id, name, age').where(name='张三').order_by('id DESC, name ASC').limit(2).query())

    print(db.table('person').order_by('id DESC, name ASC').limit(1).select('id, name, age'))
    
    print("df:", db.table('person').page(2, 4).load('id, name, age').to_df())
    print("df:", db.table('person').columns('id, name, age').where(name='张三').page(2, 4).to_df())

    print(db.table('person').where(name='张三').limit(3).load('id, name, age').to_df())
    print(db.table('person').columns('id, name, age').where(name='张三').limit(3).to_df())
    print(db.table('person').columns('id, name, age').where(name='张三').order_by('id DESC, name ASC').to_df())
    print(db.table('person').columns('id, name, age').where(name='张三').order_by('id DESC, name ASC').limit(3).to_df())
    print(db.table('person').where(name='张三').order_by('id DESC, name ASC').limit(10).load('id, name, age').to_df())

    print(db.table('person').columns('id, name, age').page(2, 3, True).select())
    print(db.table('person').columns('id, name, age').page(2, 3, True).query())
    print(db.table('person').columns('id, name, age').where(name='张三').page(2, 3, True).select())
    print(db.table('person').columns('id, name, age').where(name='张三').page(2, 3, True).query())
    
    db.close()

